from ConnectJDBCToMySql import *
import datetime


# 将大区里面的tb_qxsj里面的接口数据 做一定的处理存储进 tb_jrsj


# 逐小时逐日站点TXT可以用--从大区的tb_qxsj处理之后写入tb_jrsj：
def QxsjToJrsjZd(qxsjs):
    try:
        for item in qxsjs:
            jrsjfiletimestr = str(item[2]).rsplit("_", 1)[-1].split(".")[0]
            jrsjfiletime = datetime.datetime.strptime(str(jrsjfiletimestr), '%Y%m%d%H%M%S')

            if "_GJ_" in str(item[2]):
                jrsjstationcategory = "国家站"
            elif "_ZD_" in str(item[2]):
                jrsjstationcategory = "自动站"

            jrsjfilename = str(item[2])
            jrsjfileformat = str(item[2]).split(".")[-1]
            jrsjfilesize = item[4]
            jrsjjrinterface = item[1]
            jrsjjrtime = item[5]
            jrsjlocation = item[6].replace("\\", "\\\\")
            jrsjqxtype = item[3]

            sql = "INSERT INTO tb_jrsj(jrsj_filetime, jrsj_stationcategory, jrsj_filename, jrsj_fileformat, jrsj_filesize, jrsj_jrinterface, jrsj_jrtime, jrsj_location,jrsj_qxtype) \
                       VALUES ('%s', '%s', '%s', '%s', '%s', '%s','%s','%s','%s')" % (
                jrsjfiletime, jrsjstationcategory, jrsjfilename, jrsjfileformat, jrsjfilesize, jrsjjrinterface,
                jrsjjrtime,
                jrsjlocation, jrsjqxtype)
            # print(sql)
            try:
                c1 = tosql1()
                c1[1].execute(sql)
            except Exception as e:
                print("QxsjToJrsjZd(1) ----- ")
            finally:
                c1[1].close()
                c1[0].close()

            try:
                c2 = tosql2()
                c2[1].execute(sql)
            except Exception as e:
                print("QxsjToJrsjZd(2) ----- ")
            finally:
                c2[1].close()
                c2[0].close()

            try:
                c3 = tosql3()
                c3[1].execute(sql)
            except Exception as e:
                print("QxsjToJrsjZd(3) ----- ")
            finally:
                c3[1].close()
                c3[0].close()

    except Exception as e:
        print("QxsjToJrsjZd ----- ")


def AlarmTypeCodeToAlarmType(AlarmTypeCode):
    if AlarmTypeCode == "11B01":
        return "台风"
    elif AlarmTypeCode == "11B03":
        return "暴雨"
    elif AlarmTypeCode == "11B04":
        return "暴雪"
    elif AlarmTypeCode == "11B05":
        return "寒潮"
    elif AlarmTypeCode == "11B05":
        return "寒潮"
    elif AlarmTypeCode == "11B06":
        return "大风"
    elif AlarmTypeCode == "11B07":
        return "沙尘暴"
    elif AlarmTypeCode == "11B09":
        return "高温"
    elif AlarmTypeCode == "11B14":
        return "雷电"
    elif AlarmTypeCode == "11B15":
        return "冰雹"
    elif AlarmTypeCode == "11B16":
        return "霜冻"
    elif AlarmTypeCode == "11B17":
        return "大雾"
    elif AlarmTypeCode == "11B19":
        return "霾"
    elif AlarmTypeCode == "11B20":
        return "雷雨大风"
    elif AlarmTypeCode == "11B21":
        return "道路结冰"
    elif AlarmTypeCode == "11B22":
        return "干旱"
    elif AlarmTypeCode == "11B25":
        return "森林火灾"
    else:
        return AlarmTypeCode


# 灾害预警--从大区的tb_qxsj处理之后写入tb_jrsj：
def QxsjToJrsjAlarm(qxsjs):
    # db = pymysql.connect(host='localhost', user='root', passwd='1234', port=3306, db='dlqx_test')  # 连接数据库
    # cursor = db.cursor()
    try:
        for item in qxsjs:
            jrsjfiletimestr = str(item[2]).split("_")[1]
            jrsjfiletime = datetime.datetime.strptime(str(jrsjfiletimestr), '%Y%m%d%H%M%S')
            alarmtypecode = str(item[2]).split("_")[2]
            jrsjalarmtype = AlarmTypeCodeToAlarmType(alarmtypecode)
            jrsjfilename = str(item[2])
            jrsjfileformat = str(str(item[2])).split('.')[1]
            jrsjfilesize = item[4]
            jrsjjrinterface = item[1]
            jrsjjrtime = item[5]
            jrsjlocation = item[6].replace("\\", "\\\\")
            jrsjqxtype = item[3]

            sql = "INSERT INTO tb_jrsj(jrsj_filetime, jrsj_alarmtype, jrsj_filename, jrsj_fileformat, jrsj_filesize, jrsj_jrinterface, jrsj_jrtime, jrsj_location,jrsj_qxtype) \
                       VALUES ('%s', '%s', '%s', '%s', '%s', '%s','%s','%s','%s')" % (
                jrsjfiletime, jrsjalarmtype, jrsjfilename, jrsjfileformat, jrsjfilesize, jrsjjrinterface, jrsjjrtime,
                jrsjlocation, jrsjqxtype)
            print("插入一条灾害预警信息"+str(jrsjfilename))
            try:
                c1 = tosql1()
                c1[1].execute(sql)
            except Exception as e:
                print("QxsjToJrsjAlarm(1) ----- ")
            finally:
                c1[1].close()
                c1[0].close()

            try:
                c2 = tosql2()
                c2[1].execute(sql)
            except Exception as e:
                print("QxsjToJrsjAlarm(2) ----- ")
            finally:
                c2[1].close()
                c2[0].close()

            try:
                c3 = tosql3()
                c3[1].execute(sql)
            except Exception as e:
                print("QxsjToJrsjAlarm(3) ----- ")
            finally:
                c3[1].close()
                c3[0].close()
    except Exception as e:
        print("QxsjToJrsjAlarm ----- {e}")


# 3KM网格--从大区的tb_qxsj处理之后写入tb_jrsj：
def QxsjToJrsj3KMwg(qxsjs):
    # db = pymysql.connect(host='localhost', user='root', passwd='1234', port=3306, db='dlqx_test')  # 连接数据库
    # cursor = db.cursor()
    try:
        for item in qxsjs:
            jrsjfiletimestr = str(item[2]).split("_")[-2]
            jrsjfiletime = datetime.datetime.strptime(str(jrsjfiletimestr), '%Y%m%d%H%M')
            jrsjfilename = str(item[2])
            jrsjfileformat = str(item[2]).split(".")[-1]
            jrsjfilesize = item[4]
            jrsjjrinterface = item[1]
            jrsjjrtime = item[5]
            jrsjlocation = item[6].replace("\\", "\\\\")
            jrsjqxtype = item[3]

            sql = "INSERT INTO tb_jrsj(jrsj_filetime, jrsj_filename, jrsj_fileformat, jrsj_filesize, jrsj_jrinterface, jrsj_jrtime, jrsj_location,jrsj_qxtype) \
                       VALUES ('%s', '%s', '%s', '%s', '%s','%s','%s','%s')" % (
                jrsjfiletime, jrsjfilename, jrsjfileformat, jrsjfilesize, jrsjjrinterface, jrsjjrtime, jrsjlocation,
                jrsjqxtype)
            try:
                c1 = tosql1()
                c1[1].execute(sql)
            except Exception as e:
                print("QxsjToJrsj3KMwg(1) ----- ")
            finally:
                c1[1].close()
                c1[0].close()

            try:
                c2 = tosql2()
                c2[1].execute(sql)
            except Exception as e:
                print("QxsjToJrsj3KMwg(2) ----- ")
            finally:
                c2[1].close()
                c2[0].close()

            try:
                c3 = tosql3()
                c3[1].execute(sql)
            except Exception as e:
                print("QxsjToJrsj3KMwg(3) ----- ")
            finally:
                c3[1].close()
                c3[0].close()
    except Exception as e:
        print("QxsjToJrsj3KMwg ----- ")


def YbNameToYbfactor(filename):
    if "_TMP_" in filename:
        return "温度"
    elif "_TMAX_" in filename:
        return "最高温度"
    elif "_TMIN_" in filename:
        return "最低温度"
    elif "_RRH_" in filename:
        return "湿度"
    elif "_RHMX_" in filename:
        return "最大相对湿度"
    elif "_RHMI_" in filename:
        return "最小相对湿度"
    elif "_QPF_R24_" in filename:
        return "24小时降水"
    elif "_QPF_R12_" in filename:
        return "12小时降水"
    elif "_QPF_R06_" in filename:
        return "6小时降水"
    elif "_QPF_R03_" in filename:
        return "3小时降水"
    elif "_QPF_PPH_" in filename:
        return "降水相态"
    elif "_EDA10_" in filename:
        return "风向风速（逐3小时）"
    elif "_OEFS_ECT_" in filename:
        return "云量"
    elif "_SHYS_VIS_" in filename:
        return "能见度"
    else:
        return ""


# 5KM预报网格--从大区的tb_qxsj处理之后写入tb_jrsj：
def QxsjToJrsj5KMwg(qxsjs):
    # db = pymysql.connect(host='localhost', user='root', passwd='1234', port=3306, db='dlqx_test')  # 连接数据库
    # cursor = db.cursor()
    try:
        for item in qxsjs:
            jrsjfiletimestr = str(str(item[2])).split("_")[-1].split(".")[0]
            jrsjfiletime = datetime.datetime.strptime(str(jrsjfiletimestr[:-5]), '%Y%m%d%H%M%S')
            jrsjybfactor = YbNameToYbfactor(str(str(item[2])))
            jrsjfilename = str(str(item[2]))
            jrsjfileformat = str(str(item[2])).split(".")[-1]
            jrsjfilesize = item[4]
            jrsjjrinterface = item[1]
            jrsjjrtime = item[5]
            jrsjlocation = item[6].replace("\\", "\\\\")
            jrsjqxtype = item[3]

            # print(jrsjfiletime)

            sql = "INSERT INTO tb_jrsj(jrsj_filetime, jrsj_ybfactor, jrsj_filename, jrsj_fileformat, jrsj_filesize, jrsj_jrinterface, jrsj_jrtime, jrsj_location,jrsj_qxtype) \
                       VALUES ('%s', '%s', '%s','%s', '%s', '%s','%s','%s','%s')" % (
                jrsjfiletime, jrsjybfactor, jrsjfilename, jrsjfileformat, jrsjfilesize, jrsjjrinterface, jrsjjrtime,
                jrsjlocation, jrsjqxtype)
            try:
                c1 = tosql1()
                c1[1].execute(sql)
            except Exception as e:
                print("QxsjToJrsj5KMwg(1) ----- ")
            finally:
                c1[1].close()
                c1[0].close()

            try:
                c2 = tosql2()
                c2[1].execute(sql)
            except Exception as e:
                print("QxsjToJrsj5KMwg(2) ----- ")
            finally:
                c2[1].close()
                c2[0].close()

            try:
                c3 = tosql3()
                c3[1].execute(sql)
            except Exception as e:
                print("QxsjToJrsj5KMwg(3) ----- ")
            finally:
                c3[1].close()
                c3[0].close()
    except Exception as e:
        print(f"QxsjToJrsj5KMwg ----- {e}")


# 通用：如辐射逐小时接口--从大区的tb_qxsj处理之后写入tb_jrsj：
def QxsjToJrsjCommon(qxsjs):
    # db = pymysql.connect(host='localhost', user='root', passwd='1234', port=3306, db='dlqx_test')  # 连接数据库
    # cursor = db.cursor()
    try:
        for item in qxsjs:
            jrsjfiletimestr = str(str(item[2])).rsplit("_", 1)[-1].split(".")[0]
            jrsjfiletime = datetime.datetime.strptime(str(jrsjfiletimestr), '%Y%m%d%H%M%S')
            jrsjfilename = str(str(item[2]))
            jrsjfileformat = str(str(item[2])).split(".", 1)[-1]
            jrsjfilesize = item[4]
            jrsjjrinterface = item[1]
            jrsjjrtime = item[5]
            jrsjlocation = item[6].replace("\\", "\\\\")
            jrsjqxtype = item[3]

            sql = "INSERT INTO tb_jrsj(jrsj_filetime, jrsj_filename, jrsj_fileformat, jrsj_filesize, jrsj_jrinterface, jrsj_jrtime, jrsj_location,jrsj_qxtype) \
                       VALUES ('%s', '%s', '%s', '%s', '%s','%s','%s','%s')" % (
                jrsjfiletime, jrsjfilename, jrsjfileformat, jrsjfilesize, jrsjjrinterface, jrsjjrtime, jrsjlocation,
                jrsjqxtype)
            # print(sql)
            try:
                c1 = tosql1()
                c1[1].execute(sql)
            except Exception as e:
                print("QxsjToJrsjCommon(1) ----- ")
            finally:
                c1[1].close()
                c1[0].close()

            try:
                c2 = tosql2()
                c2[1].execute(sql)
            except Exception as e:
                print("QxsjToJrsjCommon(2) ----- ")
            finally:
                c2[1].close()
                c2[0].close()

            try:
                c3 = tosql3()
                c3[1].execute(sql)
            except Exception as e:
                print("QxsjToJrsjCommon(3) ----- ")
            finally:
                c3[1].close()
                c3[0].close()
    except Exception as e:
        print("QxsjToJrsjCommon ----- ")


# 通用：OCF--从大区的tb_qxsj处理之后写入tb_jrsj：
def QxsjToJrsjOCF(qxsjs):
    # db = pymysql.connect(host='localhost', user='root', passwd='1234', port=3306, db='dlqx_test')  # 连接数据库
    # cursor = db.cursor()
    try:
        for item in qxsjs:
            jrsjfiletimestr = str(item[2]).rsplit("_")[-2]
            jrsjfiletime = datetime.datetime.strptime(str(jrsjfiletimestr), '%Y%m%d%H%M%S')
            jrsjfilename = str(item[2])
            jrsjfileformat = str(item[2]).split(".", 1)[-1]
            jrsjfilesize = item[4]
            jrsjjrinterface = item[1]
            jrsjjrtime = item[5]
            jrsjlocation = item[6].replace("\\", "\\\\")
            jrsjqxtype = item[3]

            sql = "INSERT INTO tb_jrsj(jrsj_filetime, jrsj_filename, jrsj_fileformat, jrsj_filesize, jrsj_jrinterface, jrsj_jrtime, jrsj_location,jrsj_qxtype) \
                       VALUES ('%s', '%s', '%s', '%s', '%s','%s','%s','%s')" % (
                jrsjfiletime, jrsjfilename, jrsjfileformat, jrsjfilesize, jrsjjrinterface, jrsjjrtime, jrsjlocation,
                jrsjqxtype)
            # print(sql)
            try:
                c1 = tosql1()
                c1[1].execute(sql)
            except Exception as e:
                print("QxsjToJrsjOCF(1) ----- ")
            finally:
                c1[1].close()
                c1[0].close()

            try:
                c2 = tosql2()
                c2[1].execute(sql)
            except Exception as e:
                print("QxsjToJrsjOCF(2) ----- ")
            finally:
                c2[1].close()
                c2[0].close()

            try:
                c3 = tosql3()
                c3[1].execute(sql)
            except Exception as e:
                print("QxsjToJrsjOCF(3) ----- ")
            finally:
                c3[1].close()
                c3[0].close()
    except Exception as e:
        print("QxsjToJrsjOCF ----- ")


# qxsj_jrsjstrorestatus：标志tb_jrsj某一项是否已经同步到tb_jrsj中，默认为0,0标志未同步，1标志该项已经同步
def GetQxsjByTypeNoTb(qxsjtype):
    # db = pymysql.connect(host='localhost', user='root', passwd='1234', port=3306, db='dlqx_test')  # 连接数据库
    # cursor = db.cursor()
    qxsj = [];
    try:
        sql = "SELECT * FROM tb_qxsj WHERE qxsj_jrsjstrorestatus = 0 and qxsj_type = '%s'" % (qxsjtype)
        try:
            c1 = tosql3()
            c1[1].execute(sql)
            qxsj = c1[1].fetchall()
        except Exception as e:
            print("GetQxsjByTypeNoTb(2) ----- ")
        finally:
            c1[1].close()
            c1[0].close()
            return qxsj
    except Exception as e:
        print("GetQxsjByTypeNoTb(1) ----- ")


# 数据存储成功之后使用：将qxsj_jrsjstrorestatus标志未改为1
def SetQxsjJrsjStatus1(qxsjs):
    # db = pymysql.connect(host='localhost', user='root', passwd='1234', port=3306, db='dlqx_test')  # 连接数据库
    # cursor = db.cursor()
    try:
        for item in qxsjs:
            sql = "UPDATE  tb_qxsj SET qxsj_jrsjstrorestatus = 1 WHERE id = %s " % (item[0])
            try:
                c1 = tosql3()
                c1[1].execute(sql)
            except Exception as e:
                print("SetQxsjJrsjStatus1(2) ----- ")
            finally:
                c1[1].close()
                c1[0].close()
    except Exception as e:
        print("SetQxsjJrsjStatus1(1) ----- ")

# if __name__ == '__main__':
#     qxsjsGjxs = GetQxsjByTypeNoTb("国家站小时")
#     QxsjToJrsjZd(qxsjsGjxs)
#     qxsjsZdxs = GetQxsjByTypeNoTb("自动站小时")
#     QxsjToJrsjZd(qxsjsZdxs)
#     qxsjsWg3km = GetQxsjByTypeNoTb("3KM实时网格")
#     QxsjToJrsj3KMwg(qxsjsWg3km)
#
#     qxsjsCgqxzh = GetQxsjByTypeNoTb("常规气象灾害")
#     QxsjToJrsjCommon(qxsjsCgqxzh)
#     qxsjsHjqxzh = GetQxsjByTypeNoTb("环境气象灾害")
#     QxsjToJrsjCommon(qxsjsHjqxzh)
#     qxsjsDxjbzh = GetQxsjByTypeNoTb("电线积冰灾害")
#     QxsjToJrsjCommon(qxsjsDxjbzh)
#
#     qxsjsYb = GetQxsjByTypeNoTb("5KM预报网格")
#     QxsjToJrsj5KMwg(qxsjsYb)
#     qxsjsOCF = GetQxsjByTypeNoTb("OCF逐小时预报")
#     QxsjToJrsjOCF(qxsjsOCF)
#     qxsjsRadi = GetQxsjByTypeNoTb("辐射逐小时")
#     QxsjToJrsjCommon(qxsjsRadi)
#     qxsjsSCW = GetQxsjByTypeNoTb("SCW强对流预报")
#     QxsjToJrsjCommon(qxsjsSCW)
#
#     qxsjsZhyj = GetQxsjByTypeNoTb("灾害预警")
#     QxsjToJrsjAlarm(qxsjsZhyj)
